use ClassicDb
go

create table Category

(
	Id int primary key identity,
	CategoryName nvarchar(80) not null,
	ParentId int not null default 0
	
)


insert into Category (CategoryName) values ('服装')

insert into Category (CategoryName) values ('电脑')

insert into Category (CategoryName) values ('手机')

insert into Category (CategoryName) values ('食品')

insert into Category (CategoryName) values ('饰品')

insert into Category (CategoryName) values ('家电')

insert into Category (CategoryName,ParentId)values ('男装',(select Id from Category where CategoryName='服装'))

insert into Category (CategoryName,ParentId) values ('中山装',(select top 1 Id from Category where CategoryName='男装'))

insert into Category (CategoryName,ParentId) values ('风衣',(select top 1 Id from Category where CategoryName='男装'))

insert into Category (CategoryName,ParentId) values ('运动休闲',(select top 1 Id from Category where CategoryName='男装'))

insert into Category (CategoryName,ParentId) values ('家居服',(select top 1 Id from Category where CategoryName='男装'))

insert into Category (CategoryName,ParentId) values ('内衣',(select top 1 Id from Category where CategoryName='男装'))


;WITH CTE_A 
AS 
(
select Id,CategoryName,ParentId from Category where Id=2
union all
select b.Id,b.CategoryName,b.ParentId from Category a ,CTE_A b 
where a.Id=b.ParentId
)select * from CTE_A

go

alter function fn_parameter2(@name nvarchar(20))
returns @result table(id int, name nvarchar(20))
as
begin
	declare @x nvarchar(70)
   insert into @result(id,name) select Id,StudentName from StudentInfo where StudentName=@name
   select @x=name from @result
	insert into @result(id,name)select id,StudentName	 from StudentInfo where StudentCode=1
 return
end
select * from dbo.fn_parameter2('张三')	



;with CTE_A
AS
(
	select Id,AreaName,ParentCode from Area where ParentCode=56
	union all 
	select b.Id,b.AreaName,b.ParentCode from Area a ,CTE_A b where a.ParentCode=b.Id
)select * from CTE_A